A ragged hierarchy is a multi-level hierarchy structure that contains an unequal number of levels. For example, let's say you create a geospatial hierarchy consisting of Country - State - County - City. However, some countries in the hierarchy, like Israel, or New Zealand, don't actually have states. And many more countries, like Australia, don't have counties. We see that the hierarchy is 'uneven,' as it contains members whose parent is not in the level above that member.
Note: this feature is not available in the Community Edition.
In this case, the labels for the 'ragged' levels of these countries would appear blank, and the data cells would display the data for the parent level.
In the image below, we see a country hierarchy comprised of the following levels: Country, State, County, City. In the United States, each city has a parent County, and each county has a parent State. In Australia, however, no city has a parent country; the parent level for cities in Australia is State.
So as we see, when Australian states are expanded a blank row header is exposed, and the corresponding data cells show data at the State level (green highlights below). Meanwhile, when US states are expanded we see the County level (blue highlight).
If the default behavior for ragged hierarchies doesn't meet your needs, you can opt to hide the missing levels when constructing the hierarchy. In this scenario, any missing levels belonging to a level that you have assigned as 'ragged' will not appear in the query.
Instead, when performing drill, expand, or member selection functions, only the non-null cells will be exposed. If the next hierarchy level of a specified member doesn't exist, the members in the following level will be shows instead.
In our example, when expanding Australia's states we now see cities as the next level (green highlight below). Meanwhile, when we expand the states for the US, the next level is County (blue highlight).
Build a Ragged Hierarchy
To build a ragged hierarchy, construct the hierarchy in the usual way. Find the ragged level(s) in the hierarchy and click the drop-down beside the corresponding hierarchy icon; select 'Ragged Level' (blue highlight below). The hierarchy icon will be updated to show that it is a ragged level.
Nulls and Empties
It's important that the cells of the ragged level are null in the datasource, as opposed to empty. If these cells are empty, then the empty levels will still be displayed when a ragged hierarchy is built. If the cells in the datasource are empty, you can create a calculated column in the Data Flow to convert them to nulls using the If function: if(
if([County] = "", null, [County])
In this scenario, be sure to add the new calculated column to the ragged hierarchy, rather than the original column.